﻿using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using MarginTrading.Common;
using System.Data.OracleClient;
using System.Data;
using System;
using System.Collections.Specialized;
using System.Collections.Generic;

namespace MarginTrading.Services
{
    public class MarginStatusServices
    {
        private static MarginStatus GetMarginStatusFromDataReader(IDataReader dataReader)
        {
            MarginStatus retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new MarginStatus();

                DateTime marginDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["MARGIN_DATE"].ToString(), out marginDate))
                {
                    retVal.MarginDate = marginDate;
                }
                retVal.ClientCode = dataReader["CLIENT_CODE"].ToString();
                retVal.Agent = dataReader["AGENT"].ToString();
                retVal.Advisor = dataReader["ADVISOR"].ToString();
                retVal.ClientName = dataReader["CLIENT_NAME"].ToString();

                double dVal = 0;
                if (double.TryParse(dataReader["FACILITY_APPROVED"].ToString(), out dVal))
                {
                    retVal.FacilityApproved = dVal;
                }
                if (double.TryParse(dataReader["PORTFOLIO_VALUE"].ToString(), out dVal))
                {
                    retVal.PortfolioValue = dVal;
                }
                if (double.TryParse(dataReader["LOAN_BALANCE"].ToString(), out dVal))
                {
                    retVal.LoanBalance = dVal;
                }
                if (double.TryParse(dataReader["NET_FUTURE_BAL"].ToString(), out dVal))
                {
                    retVal.NetFutureBal = dVal;
                }
                if (double.TryParse(dataReader["PENDING_INTEREST"].ToString(), out dVal))
                {
                    retVal.PendingInterest = dVal;
                }
            }
            return retVal;
        }

        //public static MarginStatus GetMarginStatusByCodeAndSymbol(string code, string symbol)
        //{
        //    MarginStatus retVal = null;
        //    OracleConnection con = new OracleConnection();

        //    try
        //    {
        //        con.ConnectionString = Constants.DB_CONNECTION_STRING;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "select * from MarginStatus where code = '" + code + "' and symbol = '" + symbol + "'";
        //        cmd.Connection = con;

        //        using (IDataReader dataReader = cmd.ExecuteReader())
        //        {
        //            if (dataReader.Read())
        //            {
        //                retVal = GetMarginStatusFromDataReader(dataReader);
        //            }
        //        }
        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //    return retVal;
        //}

        public static MarginStatusCollection GetMarginStatusCollection()
        {
            MarginStatusCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM MARGIN_STATUS";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new MarginStatusCollection();
                    while (dataReader.Read())
                    {
                        MarginStatus item = GetMarginStatusFromDataReader(dataReader);
                        if (item != null)
                        {
                            retVal.Add(item);
                        }
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        //public static MarginStatusCollection GetMarginStatusCollectionByCode(string code)
        //{
        //    MarginStatusCollection retVal = null;
        //    OracleConnection con = new OracleConnection();

        //    try
        //    {
        //        con.ConnectionString = Constants.DB_CONNECTION_STRING;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "select * from MarginStatus where code = '" + code +"'"; //RR
        //        cmd.Connection = con;

        //        using (IDataReader dataReader = cmd.ExecuteReader())
        //        {
        //            retVal = new MarginStatusCollection();
        //            while (dataReader.Read())
        //            {
        //                MarginStatus item = GetMarginStatusFromDataReader(dataReader);
        //                if (item != null)
        //                    retVal.Add(item);
        //            }
        //        }
        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //    return retVal;
        //}

        public static DataTable GetMarginStatusCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM MARGIN_STATUS";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        //public static DataTable GetMarginStatusCollectionDataTableByCode(string code)
        //{
        //    DataTable retVal = null;
        //    OracleConnection con = new OracleConnection();

        //    try
        //    {
        //        con.ConnectionString = Constants.DB_CONNECTION_STRING;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "select * from MarginStatus where code = " + code;
        //        cmd.Connection = con;

        //        using (IDataReader dataReader = cmd.ExecuteReader())
        //        {
        //            if (dataReader != null)
        //            {
        //                retVal = new DataTable();
        //                retVal.Load(dataReader);
        //            }
        //        }
        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //    return retVal;
        //}

        public static void Add(MarginStatus MarginStatus)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = " INSERT INTO MARGIN_STATUS(MARGIN_DATE, CLIENT_CODE, AGENT, ADVISOR, CLIENT_NAME, FACILITY_APPROVED, PORTFOLIO_VALUE, LOAN_BALANCE, NET_FUTURE_BAL, PENDING_INTEREST)" +
                    "VALUES(:v1, :v2, :v3, :v4, :v5, :v6, :v7, :v8, :v9, :v10)";

                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = MarginStatus.MarginDate;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = MarginStatus.ClientCode;
                cmd.Parameters.Add(":v3", OracleType.VarChar).Value = MarginStatus.Agent;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = MarginStatus.Advisor;
                cmd.Parameters.Add(":v5", OracleType.VarChar).Value = MarginStatus.ClientName;
                cmd.Parameters.Add(":v6", OracleType.Number).Value = MarginStatus.FacilityApproved;
                cmd.Parameters.Add(":v7", OracleType.Number).Value = MarginStatus.PortfolioValue;
                cmd.Parameters.Add(":v8", OracleType.Number).Value = MarginStatus.LoanBalance;
                cmd.Parameters.Add(":v9", OracleType.Number).Value = MarginStatus.NetFutureBal;
                cmd.Parameters.Add(":v10", OracleType.Number).Value = MarginStatus.PendingInterest;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        //public static void Delete(string code, string symbol)
        //{
        //    OracleConnection con = new OracleConnection();
        //    try
        //    {
        //        con.ConnectionString = Constants.DB_CONNECTION_STRING;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "delete from MarginStatus where code = '" + code + "' and symbol = '" + symbol +"'"; //Ravin
        //        cmd.Connection = con;

        //        cmd.ExecuteNonQuery();
        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}

        //public static void Update(MarginStatus MarginStatus)
        //{
        //    OracleConnection con = new OracleConnection();

        //    try
        //    {
        //        con.ConnectionString = Constants.DB_CONNECTION_STRING;
        //        con.Open();
        //        OracleCommand cmd = new OracleCommand();
        //        cmd.CommandText = "update MarginStatus set " +
        //                          "QUANTITY = :v3, " +
        //                          "AVG_VALUE = :v4 " +
        //                          "where CODE = :v1 and SYMBOL = :v2";
        //        cmd.Parameters.Add(":v1", OracleType.VarChar).Value = MarginStatus.Code;
        //        cmd.Parameters.Add(":v2", OracleType.VarChar).Value = MarginStatus.Symbol;
        //        cmd.Parameters.Add(":v3", OracleType.Number).Value = MarginStatus.Quantity;
        //        cmd.Parameters.Add(":v4", OracleType.Number).Value = MarginStatus.AvgValue;

        //        cmd.Connection = con;
        //        cmd.ExecuteNonQuery();

        //    }
        //    catch (System.Exception exc)
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}
    }
}
